package com.aaa.qy127.dao.impl;

import com.aaa.qy127.dao.ChuKuDao;
import com.aaa.qy127.entity.ChuKu;
import com.aaa.qy127.entity.Staff;
import com.aaa.qy127.util.JDBCUtil;
import com.alibaba.fastjson.JSON;
import org.springframework.stereotype.Repository;

import javax.servlet.http.HttpServletRequest;
import java.util.List;
import java.util.Map;


@Repository("chuKuDaoImpl")
public class ChuKuDaoImpl implements ChuKuDao {

    /**
     * 查询所有的 出库信息
     * @param chuKu
     * @return
     */
    @Override
    public List<Map<String, Object>> getAllCK(ChuKu chuKu) {
        //select tc.ckid,tc.cdate,tc.uid,tc.totalprice,tc.totalcount,tc.jsr from t_ck tc INNER JOIN t_staff ts ON tc.uid = ts.id where cdate >= "2020-02-13"  and cdate <= "2020-08-01" and ts.staffName LIKE '%小%' and tc.jsr like '%李%' LIMIT 0,5  ;
        StringBuffer sql = new StringBuffer("select tc.ckid,tc.cdate,tc.uid,tc.totalprice,tc.totalcount,tc.jsr,ts.staffName,tc.status from t_ck tc INNER JOIN t_staff ts ON tc.uid = ts.id where 1 = 1");
        //ts.staffName LIKE '%小%' and tc.jsr like '%李%'
        if (chuKu.getPerson() != null && chuKu.getPerson() != ""){
            sql.append(" and ts.staffName like '%").append(chuKu.getPerson() + "%'");
        }
        if (chuKu.getJsr() != null && chuKu.getJsr() != ""){
            sql.append(" and tc.jsr like '%").append(chuKu.getJsr()+"%'");
        }
        //cdate >= "2020-02-13"  and cdate <= "2020-08-01"
        if (chuKu.getBeginDate() != null && chuKu.getBeginDate() != ""){
            sql.append(" and cdate >= '"+chuKu.getBeginDate()+"'");
        }
        if (chuKu.getOverDate() !=null && chuKu.getOverDate() != ""){
            sql.append(" and cdate <= '"+chuKu.getOverDate()+"'");
        }
        sql.append(" limit ?,?");
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql.toString(),(chuKu.getPage()-1)*chuKu.getLimit(),chuKu.getLimit());
        //System.out.println(sql);
        return maps;
    }

    /**
     * 查询所有的出库信息的数量
     * @param chuKu
     * @return
     */
    @Override
    public int getAllCount(ChuKu chuKu) {
        StringBuffer sql = new StringBuffer("select count(*) from t_ck tc INNER JOIN t_staff ts ON tc.uid = ts.id  where 1 = 1");
        if (chuKu.getPerson() != null && chuKu.getPerson() != ""){
            sql.append(" and ts.staffName like '%").append(chuKu.getPerson() + "%'");
        }
        if (chuKu.getJsr() != null && chuKu.getJsr() != ""){
            sql.append(" and tc.jsr like '%").append(chuKu.getJsr()+"%'");
        }
        //cdate >= "2020-02-13"  and cdate <= "2020-08-01"
        if (chuKu.getBeginDate() != null && chuKu.getBeginDate() != ""){
            sql.append(" and cdate >= '"+chuKu.getBeginDate()+"'");
        }
        if (chuKu.getOverDate() !=null && chuKu.getOverDate() != ""){
            sql.append(" and cdate <= '"+chuKu.getOverDate()+"'");
        }
        List<List> lists = JDBCUtil.queryList(sql.toString());
        Object o = lists.get(0).get(0);
        int i = Integer.parseInt(o + "");
        return i;
    }

    /**
     * 按照出库id 查询 出库详情信息
     * @param chuKu
     * @return
     */
    @Override
    public List<Map<String, Object>> getAllChuKu(ChuKu chuKu) {
        String sql = "select td.cdid,td.bookid,tb.bookname,tb.author,tb.publisher,td.count,td.ckid,tc.jsr,tc.cdate,td.outprice,ts.staffName,tt.typename,tc.uid from t_book tb INNER JOIN t_ckdetail td ON td.bookid = tb.bookid \n" +
                "INNER JOIN t_ck  tc ON tc.ckid = td.ckid \n" +
                "INNER JOIN t_staff ts ON ts.id = tc.uid \n" +
                "INNER JOIN t_type tt ON tb.typeid = tt.typeid\n" +
                "where tc.ckid = ?";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql, chuKu.getCkid());
        //System.out.println(maps);
        if (maps.size() >0){
            return maps;
        }
        return null;
    }

    /**
     * 查询出库详情信息数量
     * @return
     */
    @Override
    public int getAllChuKuCount(ChuKu chuKu) {
        String sql = "select COUNT(*) from t_book tb INNER JOIN t_ckdetail td ON td.bookid = tb.bookid \n" +
                "INNER JOIN t_ck  tc ON tc.ckid = td.ckid \n" +
                "INNER JOIN t_staff ts ON ts.id = tc.uid \n" +
                "INNER JOIN t_type tt ON tb.typeid = tt.typeid\n" +
                "where tc.ckid = ?";
        List<List> lists = JDBCUtil.queryList(sql, chuKu.getCkid());
        if (lists.size() >0){
            Object o = lists.get(0).get(0);
            int i = Integer.parseInt(o + "");
            return i;
        }
        return 0;
    }

    /**
     * 插入一条出库信息
     * @param totalprice
     * @param totalcount
     * @param jsr
     * @return
     */
    @Override
    public int insertChuKu(String totalprice, String totalcount, String jsr, HttpServletRequest request) {
        String sql = "insert into t_ck_jl values(null,null,?,?,?,?,0,null)";
        Staff staff = (Staff)request.getSession().getAttribute("staff");
        int i = JDBCUtil.getPrimaryKey(sql, staff.getId(), totalprice, totalcount, jsr);
        return i;
    }

    /**
     * 向 出库详情表中添加记录
     * @param ckid
     * @param chukuxinxi
     * @return
     */
    @Override
    public void insertAllChuku(int ckid, String chukuxinxi) {
        //把 String 类型的数据 修改成 List<Map<String,Object>> 类型
        List<Map<String,Object>> parse = (List<Map<String,Object>>) JSON.parse(chukuxinxi);
        //循环插入多条数据
        for (Map<String, Object> map : parse) {
            String sql = "insert into t_ckjldetail VALUES(null,?,?,?,?)";
            int i = JDBCUtil.update(sql, map.get("bookname"), map.get("count"), ckid, map.get("outprice"));
        }
    }


}
